"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

import traceback
from msnp.logger import Logger
from msnp.model import MediaModel
from msnp.service.utils import StringCompare
from msnp.persistence.mysql import DBErrorHandler

########
#tables#
########
TABLE_TRACKS = 'tracks'
TABLE_ALBUMS = 'albums'
TABLE_ARTISTS = 'artists'
TABLE_GENRES = 'genres'
TABLE_LYRICS = 'lyrics'
TABLE_URLS = 'urls'
TABLE_STATISTICS = 'statistics'
TABLE_YEARS = 'years'
TABLE_COMPOSERS = 'composers'

##########################
#columns for tracks table#
##########################

COL_TRACKS_ID = 'id'
COL_TRACKS_SELECT_ID = 'track_id'
COL_TRACKS_TITLE = 'title'
COL_TRACKS_COMMENT = 'comment'
COL_TRACKS_TRACKNUMBER = 'tracknumber'
COL_TRACKS_DISCNUMBER = 'discnumber'
COL_TRACKS_BITRATE = 'bitrate'
COL_TRACKS_LENGTH = 'length'
COL_TRACKS_FILESIZE = 'filesize'
COL_TRACKS_BPM = 'bpm'
COL_TRACKS_URL = 'url'
COL_TRACKS_YEAR = 'year'
COL_TRACKS_GENRE = 'genre'
COL_TRACKS_ALBUM = 'album'
COL_TRACKS_ARTIST = 'artist'
COL_TRACKS_COMPOSER = 'composer'

###########################
#columns for artists table#
###########################

COL_ARTISTS_ID = 'id'
COL_ARTISTS_SELECT_ID = 'artist_id'
COL_ARTISTS_NAME = 'name'
COL_ARTISTS_SELECT_NAME = 'artist_name'

##########################
#columns for albums table#
##########################

COL_ALBUMS_ID = 'id'
COL_ALBUMS_SELECT_ID = 'album_id'
COL_ALBUMS_NAME = 'name'
COL_ALBUMS_SELECT_NAME = 'album_name'

#########################
#columns for genre table#
#########################

COL_GENRE_ID = 'id'
COL_GENRE_SELECT_ID = 'genre_id'
COL_GENRE_NAME = 'name'
COL_GENRE_SELECT_NAME = 'genre_name'

#########################
#columns for years table#
#########################

COL_YEARS_ID = 'id'
COL_YEARS_NAME = 'name'
COL_YEARS_SELECT_NAME = 'year_name'

##########################
#columns for lyrics table#
##########################

COL_LYRICS_LYRICS = 'lyrics'
COL_LYRICS_URL = 'url'
COL_LYRICS_ID = 'id'

##############################
#columns for statistics table#
##############################

COL_STAT_SCORE = 'score'
COL_STAT_RATING = 'rating'
COL_STAT_PLAYCOUNT = 'playcount'
COL_STAT_URL = 'url'
COL_STAT_ID = 'id'

########################
#columns for urls table#
########################

COL_URLS_ID = 'id'
COL_URLS_RPATH = 'rpath'

#############################
#columns for composers table#
#############################

COL_COMP_ID = 'id'
COL_COMP_SELECT_ID = 'composer_id'
COL_COMP_NAME = 'name'
COL_COMP_SELECT_NAME = 'composer_name'


def existsSchemaAndTables(database, cursor, schema):
    """Check whether there exists a player database with the given schema and the correct tables.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String representing the schema name.
        
    Returns:
        True is returned, if there exists a media database with the given schema and the correct tables, False otherwise.
    """
    try:
        query = "select table_name from information_schema.tables where table_schema = '" + schema + "'"
        Logger.mediaDBinfo(query)
        cursor.execute(query)
        
        existingTables = []
        while 1:
            result = cursor.fetchone()
            if result == None or len(result) == 0:
                break
            existingTables.append(result[0])
        
        if existingTables.__contains__(TABLE_ALBUMS):
            if existingTables.__contains__(TABLE_ARTISTS):
                if existingTables.__contains__(TABLE_COMPOSERS):
                    if existingTables.__contains__(TABLE_GENRES):
                        if existingTables.__contains__(TABLE_LYRICS):
                            if existingTables.__contains__(TABLE_STATISTICS):
                                if existingTables.__contains__(TABLE_TRACKS):
                                    if existingTables.__contains__(TABLE_URLS):
                                        if existingTables.__contains__(TABLE_YEARS):
                                            return True
        return False
    except:
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.CHECK, traceback.format_exc())
    return False



def executeQuery(database, cursor, query):
    """Executes the given query and commits the changes.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        query: A String containing the query to be executed.
    """
    try:
        cursor.execute(query)
        database.commit()
    except:
        Logger.info("error while executing query: " + query)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.UNKNOWN, traceback.format_exc())
        
def __selectLyricsForUrl(database, cursor, schema, url):
    """Selects from the given player database the lyrics of the track that corresponds to the given file url.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String containing the name of the schema where the query should be executed.
        url: A String containing the track's file url.
    
    Returns:
        A String containing the retrieved url is returned, or None if an error occurred.
    """
    try:
        query = __getQuerySelectLyricsForUrl(schema)
        cursor.execute(query, [url])
        result = cursor.fetchone()
        if result != None:
            return result[0]
    except:
        Logger.info("Error: unable to select lyrics for url")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None
        
def selectTrackIdsForTitleArtistAndCollection(database, cursor, schema, title, artistName, collectionName):
    """Selects from the given player database the ids for all tracks that correspond to the given title, artist name and collection name.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String containing the name of the schema where the query should be executed.
        title: A String containing the title of the track.
        artistName: A String containing the track's artist name.
        collectionName: A String containing the track's collection name.
    
    Returns:
        A list of integers representing the ids is returned, or None if an error occurred.
    """
    audios = selectAudio(database, cursor, schema, None)
    ids = []
    if audios != None:
        for audio in audios:
            isTitleEquals = StringCompare.isEquals(audio.getMedia().getTitle(), title)
            isArtistEquals = StringCompare.isEquals(audio.getArtist().getName(), artistName)
            isCollectionEquals = True
            if audio.getMedia().getCollection() != None and audio.getMedia().getCollection().getName() != None and len(audio.getMedia().getCollection().getName()) > 0 and collectionName != None and len(collectionName) > 0:
                isCollectionEquals = StringCompare.isEquals(audio.getMedia().getCollection().getName(), collectionName)
            if isTitleEquals and isArtistEquals and isCollectionEquals:
                ids.append(audio.getId())
    if len(ids) == 0:
        ids = None
    return ids
        
def selectFileUrlForTrackId(database, cursor, schema, trackId):
    """Selects from the given player database the file url for the given track id.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String containing the name of the schema where the query should be executed.
        trackId: An integer containing the id of the track.
    
    Returns:
        A String containing the retrieved url is returned, or None if an error occurred.
    """
    try:
        query = __getQuerySelectFileUrlForTrackId(schema)
        print query
        cursor.execute(query, [trackId])
        result = cursor.fetchone()
        if result != None:
            s = result[0][1:]
            try:
                s = unicode(s)
            except:
                Logger.info("unable to convert to utf-8: '" + s + "'")
            return s
    except:
        Logger.info("Error: unable to select file url for trackid")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None
        
def selectAudio(database, cursor, schema, trackId):
    """Selects from the given player database the audio with the given track id or all audios if trackId is None.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String containing the name of the schema where the query should be executed.
        trackId: An integer containing the id of the track.
    
    Returns:
        A list of Audio objects containing the retrieved audios of the player database, or None if an error occurred.
    """
    try:
        query = __getQuerySelectAudio(schema, trackId)
        cursor.execute(query)
        audios = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            trackId = None
            if result[0] != None:
                trackId = int(result[0])
            title = result[1]
            comment = result[2]
            tracknumber = None
            if result[3] != None:
                tracknumber = int(result[3])
            discnumber = None
            if result[4] != None:
                discnumber = int(result[4])
            bitrate = None
            if result[5] != None:
                bitrate = int(result[5])
            length = None
            if result[6] != None:
                length = int(result[6])
            filesize = None
            if result[7] != None:
                filesize = int(result[7])
            bpm = None
            if result[8] != None:
                bpm = float(result[8])
            artistName = result[9]
            albumName = result[10]
            genreName = result[11]
            year = result[12]
            composer = result[13]
            lyrics = result[14]
            score = None
            if result[15] != None:
                score = float(result[15])
            rating = None
            if result[16] != None:
                rating = int(result[16])
            playcount = None
            if result[17] != None:
                playcount = int(result[17])
            audio = MediaModel.Audio.createAudioFromAmarok(title, comment, tracknumber, discnumber, bitrate, length, filesize, bpm, artistName, albumName, genreName, year, composer, lyrics, score, rating, playcount, trackId)
            audios.append(audio)
        return audios
    except:
        Logger.info("Error: unable to select audios from amarok db")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

#for update changes - it is enough to select one matching track from the 
#amarok database to execute the changes in the media db
def selectTrackId(database, cursor, schema, tablename, rowId):
    """Selects from the given player database the id of the track that has an entry in the given row in the given table.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String containing the name of the schema where the query should be executed.
        tablename: A String containing the name of a certain table of the player database.
        rowId: An integer value containing a certain row number.
    
    Returns:
        An integer value containing the retrieved track id is returned, or None if an error occurred.
    """
    query = None
    #if change came from track table -> we have already the id
    if tablename == TABLE_TRACKS:
        return rowId
    else:
        if tablename == TABLE_ALBUMS:
            query = __getQuerySelectTrackIdForAlbum(schema, rowId)
        elif tablename == TABLE_ARTISTS:
            query = __getQuerySelectTrackIdForArtist(schema, rowId)
        elif tablename == TABLE_COMPOSERS:
            query = __getQuerySelectTrackIdForComposer(schema, rowId)
        elif tablename == TABLE_GENRES:
            query = __getQuerySelectTrackIdForGenre(schema, rowId)
        elif tablename == TABLE_LYRICS:
            query = __getQuerySelectTrackIdForLyric(schema, rowId)
        elif tablename == TABLE_STATISTICS:
            query = __getQuerySelectTrackIdForStatistic(schema, rowId)
        if query != None:
            return __selectTrackIdForQuery(database, cursor, query)
    return None

def __selectTrackIdForQuery(database, cursor, query):
    """Selects from the given player database the id of the track that is retrieved from the given query.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String containing the name of the schema where the query should be executed.
        query: A String containing the query to execute.
    
    Returns:
        An integer value containing the retrieved track id is returned, or None if an error occurred.
    """
    try:
        cursor.execute(query)
        result = cursor.fetchone()
        if result != None:
            trackId = result[0]
            return trackId
    except:
        Logger.info("Error: unable to fetch trackId for query: '" + query + "'")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None


#####################
## INSERT - UPDATE ##
#####################

def insertOrUpdateLyricsForUrl(database, cursor, schema, lyrics, url):
    """Inserts or updates in the given player database the lyrics of the track that corresponds to the given url.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String containing the name of the schema where the query should be executed.
        lyrics: A String containing the lyrics to insert or update.
        url: A String containing the file url of the track which lyrics should be updated or inserted.
    
    Returns:
        True is returned if the update or insertion was successful, False otherwise.
    """
    try:
        if lyrics != None and len(lyrics) > 0:
            if __selectLyricsForUrl(database, cursor, schema, url) != None:
                query = __getQueryUpdateLyricsForUrl(schema)
            else:
                query = __getQueryInsertLyrics(schema)
            cursor.execute(query, [lyrics, url])
            database.commit()
        else:
            Logger.info("no need to update or insert lyrics")
        return True
    except:
        Logger.info("Error: unable to update or insert lyrics")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False

####################
## SELECT_QUERIES ##
####################

def __getQuerySelectFileUrlForTrackId(schema):
    """Creates a query for selecting the file url for a given track id.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select u." + COL_URLS_RPATH
    query += " from " + schema + "." + TABLE_TRACKS + " t"
    query += " join " + schema + "." + TABLE_URLS + " u on t." + COL_TRACKS_URL + " = u." + COL_URLS_ID
    query += " where t." + COL_TRACKS_ID + " = %s"
    return query

def __getQuerySelectAudio(schema, trackId=None):
    """Creates a query for selecting the track with the given track id or, if the track id is None, all tracks are selected.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        trackId: An integer value containing the id of the track that should be retrieved, or None if all tracks should be retrieved. Default None. 
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select t." + COL_TRACKS_ID + ", t." + COL_TRACKS_TITLE + ", t." + COL_TRACKS_COMMENT
    query += ", t." + COL_TRACKS_TRACKNUMBER + ", t." + COL_TRACKS_DISCNUMBER + ", t." + COL_TRACKS_BITRATE + ", t." + COL_TRACKS_LENGTH
    query += ", t." + COL_TRACKS_FILESIZE + ", t." + COL_TRACKS_BPM
    query += ", a." + COL_ARTISTS_NAME + " as '" + COL_ARTISTS_SELECT_NAME
    query += "', al." + COL_ALBUMS_NAME + " as '" + COL_ALBUMS_SELECT_NAME
    query += "', g." + COL_GENRE_NAME + " as '" + COL_GENRE_SELECT_NAME + "', y." + COL_YEARS_NAME + " as '" + COL_YEARS_SELECT_NAME
    query += "', c." + COL_COMP_NAME + " as '" + COL_COMP_SELECT_NAME
    query += "', l." + COL_LYRICS_LYRICS + ", s." + COL_STAT_SCORE + ", s." + COL_STAT_RATING + ", s." + COL_STAT_PLAYCOUNT
    query += " from " + schema + "." + TABLE_TRACKS + " t"
    query += " join " + schema + "." + TABLE_ARTISTS + " a on t." + COL_TRACKS_ARTIST + " = a." + COL_ARTISTS_ID
    query += " join " + schema + "." + TABLE_ALBUMS + " al on t." + COL_TRACKS_ALBUM + " = al." + COL_ALBUMS_ID
    query += " left join " + schema + "." + TABLE_GENRES + " g on t." + COL_TRACKS_GENRE + " = g." + COL_GENRE_ID
    query += " left join " + schema + "." + TABLE_YEARS + " y on t." + COL_TRACKS_YEAR + " = y." + COL_YEARS_ID
    query += " left join " + schema + "." + TABLE_COMPOSERS + " c on t." + COL_TRACKS_COMPOSER + " = c." + COL_COMP_ID
    query += " left join " + schema + "." + TABLE_URLS + " u on t." + COL_TRACKS_URL + " = u." + COL_URLS_ID
    query += " left join " + schema + "." + TABLE_LYRICS + " l on u." + COL_URLS_RPATH + " = l." + COL_LYRICS_URL
    query += " left join " + schema + "." + TABLE_STATISTICS + " s on t." + COL_TRACKS_URL + " = s." + COL_STAT_URL
    if trackId != None:
        query += " where t." + COL_TRACKS_ID + " = " + str(trackId)
    return query

def __getQuerySelectTrackIdForAlbum(schema, rowId):
    """Creates a query for selecting the track id that has an entry in the given rowId of the album table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        rowId: An integer value containing the row number.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select t." + COL_TRACKS_ID
    query += " from " + schema + "." + TABLE_ALBUMS + " a join " + schema + "." + TABLE_TRACKS + " t on a." + COL_ALBUMS_ID + " = t." + COL_TRACKS_ALBUM
    query += " where a." + COL_ALBUMS_ID + "=" + str(rowId)
    query += " order by t." + COL_TRACKS_ID + " asc"
    query += " limit 0,1"
    return query

def __getQuerySelectLyricsForUrl(schema):
    """Creates a query for selecting the lyrics of the track that corresponds to the given file url.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select l." + COL_LYRICS_LYRICS
    query += " from " + schema + "." + TABLE_LYRICS + " l"
    query += " where l." + COL_LYRICS_URL + " = %s"
    return query

def __getQuerySelectTrackIdForArtist(schema, rowId):
    """Creates a query for selecting the track id that has an entry in the given rowId of the artist table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        rowId: An integer value containing the row number.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select t." + COL_TRACKS_ID
    query += " from " + schema + "." + TABLE_ARTISTS + " a join " + schema + "." + TABLE_TRACKS + " t on a." + COL_ARTISTS_ID + " = t." + COL_TRACKS_ARTIST
    query += " where a." + COL_ARTISTS_ID + "=" + str(rowId)
    query += " order by t." + COL_TRACKS_ID + " asc"
    query += " limit 0,1"
    return query

def __getQuerySelectTrackIdForComposer(schema, rowId):
    """Creates a query for selecting the track id that has an entry in the given rowId of the composer table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        rowId: An integer value containing the row number.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select t." + COL_TRACKS_ID
    query += " from " + schema + "." + TABLE_COMPOSERS + " c join " + schema + "." + TABLE_TRACKS + " t on c." + COL_COMP_ID + " = t." + COL_TRACKS_COMPOSER
    query += " where c." + COL_COMP_ID + "=" + str(rowId)
    query += " order by t." + COL_TRACKS_ID + " asc"
    query += " limit 0,1"
    return query

def __getQuerySelectTrackIdForGenre(schema, rowId):
    """Creates a query for selecting the track id that has an entry in the given rowId of the genre table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        rowId: An integer value containing the row number.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select t." + COL_TRACKS_ID
    query += " from " + schema + "." + TABLE_GENRES + " g join " + schema + "." + TABLE_TRACKS + " t on g." + COL_GENRE_ID + " = t." + COL_TRACKS_GENRE
    query += " where g." + COL_GENRE_ID + "=" + str(rowId)
    query += " order by t." + COL_TRACKS_ID + " asc"
    query += " limit 0,1"
    return query

def __getQuerySelectTrackIdForLyric(schema, rowId):
    """Creates a query for selecting the track id that has an entry in the given rowId of the lyrics table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        rowId: An integer value containing the row number.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select t." + COL_TRACKS_ID
    query += " from " + schema + "." + TABLE_LYRICS + " l join " + schema + "." + TABLE_URLS + " u on l." + COL_LYRICS_URL + " = u." + COL_URLS_RPATH
    query += " join " + schema + "." + TABLE_TRACKS + " t on u." + COL_URLS_ID + " = t." + COL_TRACKS_URL
    query += " where l." + COL_LYRICS_ID + "=" + str(rowId)
    query += " order by t." + COL_TRACKS_ID + " asc"
    query += " limit 0,1"
    return query

def __getQuerySelectTrackIdForStatistic(schema, rowId):
    """Creates a query for selecting the track id that has an entry in the given rowId of the statistics table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        rowId: An integer value containing the row number.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "select t." + COL_TRACKS_ID
    query += " from " + schema + "." + TABLE_STATISTICS + " s join " + schema + "." + TABLE_TRACKS + " t on s." + COL_STAT_URL + " = t." + COL_TRACKS_URL
    query += " where s." + COL_STAT_ID + "=" + str(rowId)
    query += " order by t." + COL_TRACKS_ID + " asc"
    query += " limit 0,1"
    return query

####################
## UPDATE-QUERIES ##
####################

def __getQueryUpdateLyricsForUrl(schema):
    """Creates a query for updating the lyrics of the track that corresponds to the given file url.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "update " + schema + "." + TABLE_LYRICS
    query += " set " + COL_LYRICS_LYRICS + " = %s where " + COL_LYRICS_URL + " = %s"
    return query

def __getQueryInsertLyrics(schema):
    """Creates a query for inserting the lyrics of the track that corresponds to the given file url.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query is returned.
    """
    query = "insert into " + schema + "." + TABLE_LYRICS
    query += " (" + COL_LYRICS_LYRICS + "," + COL_LYRICS_URL + ") values "
    query += "(%s, %s)"
    return query
    
